﻿using DotNetCommon;
using DotNetCommon.Extensions;
using System;
using System.Collections.Concurrent;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace DBUtil.Provider.SqlServer;

public class SqlServerIdSNOGenerator : IIdSNOGenerator
{
    /// <summary>
    /// 基于数据库的Id生成控制的存储过程名称,默认为"__proc_generateid"
    /// </summary>
    public string DBCacheGeneratorIdProcedureName { get; private set; }

    /// <summary>
    /// 基于数据库的Id生成控制的表名称,默认为"__generator_id"
    /// </summary>
    public string DBCacheGeneratorIdTableName { get; private set; }

    /// <summary>
    /// 基于数据库的Id生成控制的日志表名称,默认为"__generator_err_log"
    /// </summary>
    public string DBCacheGeneratorLogTableName { get; private set; }

    /// <summary>
    /// 基于数据库的流水号生成控制的存储过程名称,默认为"__proc_generatesno"
    /// </summary>
    public string DBCacheGeneratorSNOProcedureName { get; private set; }

    /// <summary>
    /// 基于数据库的流水号生成控制的表名称,默认为"__generator_sno"
    /// </summary>
    public string DBCacheGeneratorSNOTableName { get; private set; }

    public SqlServerIdSNOGenerator(string dBCacheGeneratorIdProcedureName = null
        , string dBCacheGeneratorIdTableName = null
        , string dBCacheGeneratorLogTableName = null
        , string dBCacheGeneratorSNOProcedureName = null
        , string dBCacheGeneratorSNOTableName = null)
    {
        DBCacheGeneratorIdProcedureName = dBCacheGeneratorIdProcedureName.IfNullOrEmptyUse("__proc_generateid");
        DBCacheGeneratorIdTableName = dBCacheGeneratorIdTableName.IfNullOrEmptyUse("__generator_id");
        DBCacheGeneratorLogTableName = dBCacheGeneratorLogTableName.IfNullOrEmptyUse("__generator_err_log");
        DBCacheGeneratorSNOProcedureName = dBCacheGeneratorSNOProcedureName.IfNullOrEmptyUse("__proc_generatesno");
        DBCacheGeneratorSNOTableName = dBCacheGeneratorSNOTableName.IfNullOrEmptyUse("__generator_sno");
    }

    private static readonly ConcurrentDictionary<string, bool> _initGenerators = [];
    /// <summary>
    /// 确保已经调用过<seealso cref="InitDBCacheGenerator"/>方法
    /// </summary>
    private async Task EnsureInitGenerator(DBAccess db)
    {
        if (!_initGenerators.TryGetValue(db.DBConn, out _))
        {
            await AsyncLocker.LockAsync($"EnsureInitGenerator_{db.DBConn}", async () =>
            {
                if (!_initGenerators.TryGetValue(db.DBConn, out _))
                {
                    var b = await InitDBCacheGenerator(db);
                    if (b) _initGenerators.TryAdd(db.DBConn, true);
                }
            });
        }
    }

    private async Task<bool> InitDBCacheGenerator(DBAccess db)
    {
        if (!await db.IsTableOrViewExistAsync(DBCacheGeneratorLogTableName))
        {
            #region 新建表 Settings.DBCacheGeneratorLogTableName
            await db.ExecuteSqlAsync(
            $"""
            create table {DBCacheGeneratorLogTableName}(
                id int identity(1,1),--错误序号
                err_proc  varchar(200),--出现错误的存储过程或 触发器的名称
                lock_str  varchar(200),--申请的锁资源锁
                err_num   int,--错误号
                err_severity  int,--严重性
                err_state int,--错误状态号  
                err_line  int,--导致错误的例程中的行号
                err_msg   varchar(200),--错误消息的完整文本
                err_time  datetime --错误发生时间
            )
            """);
            #endregion
        }
        if (!await db.IsTableOrViewExistAsync(DBCacheGeneratorIdTableName))
        {
            #region 新建表 Settings.DBCacheGeneratorIdTableName
            await db.ExecuteSqlAsync(
            $"""
            create table {DBCacheGeneratorIdTableName}(
                tablename varchar(50), -- 引用表名
                colname varchar(50), -- 引用列名
                currentid bigint,
                primary key(tablename,colname)
            )
            """);
            #endregion
        }
        if (!await db.IsProcedureExistAsync(DBCacheGeneratorIdProcedureName))
        {
            var procName = DBCacheGeneratorIdProcedureName;
            var tableName = DBCacheGeneratorIdTableName;
            var errTableName = DBCacheGeneratorLogTableName;
            #region 新建存储过程 Settings.DBCacheGeneratorIdProcedureName
            await db.ExecuteSqlAsync($@"
-- Version=2021-09-19
/*
正常情况:
	1. 无缓存,实际表里也没匹配到
	2. 无缓存,实际表里匹配到
	3. 有缓存
异常情况:
	1. 实际表不存在等,造成查询报错
	2. 获取锁超时
*/
/* 调用示例
exec {procName}      
	@tablename='test',      
	@colname='id', 
	@count=1
*/
create proc {procName}
@tablename varchar(200),
@colname varchar(200),
@count int=1
as
begin tran
    declare @current bigint,@sql nvarchar(1000),@lockstr nvarchar(200), @result int;
	declare @err_message varchar(500),@err_serverity varchar(50),@err_state varchar(50);
    begin
	    -- 申请锁,20秒
	    set @lockstr='dbutil:newid:'+@tablename+'_'+@colname;
	    EXEC @result =sp_getAppLock @resource=@lockstr,@lockMode='Exclusive',@lockOwner='Transaction',@lockTimeout='20000';
	    if(@result >= 0)
	    begin
		    begin try
			    --先从id表里查找
			    set @current = (select top 1 currentid from {tableName} where tablename = @tablename and colname = @colname)
			    if @current is null
			      --表里尚未缓存,从实际表里查找id列的最大值
			      begin
				    set @sql='select @current=max('+@colname+') from '+@tablename	
				    exec sp_executesql @sql,N'@current bigint output',@current output
				    if(@current is null)
					    --实际表里也没有值
					    begin
                            set @current=@count
						    insert into {tableName}(tablename, colname, currentid) values(@tablename, @colname, @current)
						    select @current
					    end
				    else
					    --实际表里有值
					    begin
						    set @current+=@count
						    insert into {tableName}(tablename, colname, currentid) values(@tablename, @colname, @current)
						    select @current
					    end    
			      end
			    else
				    --表里已经缓存
				    begin
					    set @current += @count
					    update {tableName} set currentid = @current where tablename = @tablename and colname = @colname
					    select @current
			       end
			    exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
                commit
                return
		    end try
		    begin catch
			    exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
				select @err_message=ERROR_MESSAGE(),@err_serverity=ERROR_SEVERITY(),@err_state=ERROR_STATE();
				RAISERROR (@err_message, @err_serverity,@err_state );
                rollback;
			    INSERT INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
				    values('{procName}',@lockstr,ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(), ERROR_LINE() ,ERROR_MESSAGE(),GetDate());                 
                return;
		    end catch
	    end
	    else
	    begin
            rollback;
		    INSERT INTO {errTableName}(err_proc,lock_str,err_msg,err_time) 
				    values('{procName}',@lockstr,'20秒内未获取到锁,申请锁返回:'+Convert(varchar,@result),GetDate());
			select @err_message='20秒内未获取到锁:'+@lockstr,@err_serverity=10,@err_state=1;
			RAISERROR (@err_message, 16,1 );
            return;
	    end
    end");
            #endregion
        }
        if (!await db.IsTableOrViewExistAsync(DBCacheGeneratorSNOTableName))
        {
            #region 新建表(流水号生成) Settings.DBCacheGeneratorSNOTableName
            await db.ExecuteSqlAsync($@"
create table {DBCacheGeneratorSNOTableName}
(
	tablename varchar(50), -- 引用表名
	colname varchar(50),  -- 引用列名
	statictext varchar(200),
	machineidstr varchar(50),
	nowstr varchar(100),
	currentno bigint,
	primary key(tablename,colname,statictext)
)");
            #endregion
        }
        if (!await db.IsProcedureExistAsync(DBCacheGeneratorSNOProcedureName))
        {
            var procName = DBCacheGeneratorSNOProcedureName;
            var tableName = DBCacheGeneratorSNOTableName;
            var errTableName = DBCacheGeneratorLogTableName;
            #region 新建存储过程(流水号生成) Settings.DBCacheGeneratorSNOProcedureName
            await db.ExecuteSqlAsync($@"
-- Version=2021-09-19
/*
正常情况:
	1. 无缓存,实际表里也没匹配到
	2. 无缓存,实际表里匹配到
	3. 有缓存,但时间戳对不上
	4. 有缓存,时间戳也对的上
异常情况:
	1. 实际表不存在等,造成查询报错
	2. 获取锁超时
*/

/* 调用实例
exec {procName}      
	@tablename='test',      
	@colname='sno',      
	@statictext='SNO',      
	@machineidstr=0000,      
	@nowstr='2021-09-19 00:00:00',       
	@likestr='SNO20210919%',      
	@startindex=11,      
	@count=1
*/

-- 其实, 除了自增的序列号其他部分已经在程序中计算完成,这个要根据程序计算的结果求出对应的序列号
create proc {procName}
@tablename varchar(50),-- 实际表名(关键值)
@colname varchar(50),-- 实际列名(关键值)
@statictext varchar(200),-- 静态文本,即: 除了时间戳和序列号的其他部分(包含机器id标识) (关键值)
@machineidstr varchar(50),-- 机器id标识,仅用作记录
@nowstr varchar(200),-- 当前流水号对应的时间戳字符串(格式为: yyyy-MM-dd HH:mm:ss)
@likestr varchar(200),-- 程序中计算的除了自增序列号的其他部分,如: 'SNO20210919%', 用于无缓存时从真实表中匹配(关键值)
@startindex int, -- 用于从实际表中查出流水号中截取序列号部分, 注意:程序传入的startindex是以0起始,而数据库中是以1起始(关键值) 
@count int=1 --批量生成时的数量
as
declare 
	@current bigint,
	@tempno nvarchar(200),
	@sql nvarchar(1000),
	@templen int,
	@lockstr nvarchar(200),
	@result int,
	@currentnowstr varchar(200);
declare @err_message varchar(500),@err_serverity varchar(50),@err_state varchar(50);

begin tran
-- 申请锁,20秒
set @lockstr='dbutil:newsno:'+@tablename+'_'+@colname+'_'+@statictext;
EXEC @result =sp_getAppLock @resource=@lockstr,@lockMode='Exclusive',@lockOwner='Transaction',@lockTimeout='20000';
if(@result >= 0)
	begin
		-- 获取到了锁
		begin try
			--先从流水号表里查找
			select top 1 @current=currentno,@currentnowstr=nowstr from {tableName} where tablename = @tablename and colname = @colname and statictext=@statictext
			if @current is null
			    --表里尚未缓存,从实际表里查找流水号列的最大值
			    begin
			    set @sql='select top 1 @tempno='+@colname+' from '+@tablename +' where '+@colname+' like '''+@likestr+''' order by '+@colname +' desc'
			    exec sp_executesql @sql,N'@tempno nvarchar(200) output',@tempno output
			    if(@tempno is null)
				    -- 实际表中也找不到参照
				    begin
					    insert into {tableName}(tablename, colname,statictext,machineidstr,nowstr, currentno) values(@tablename, @colname,@statictext,@machineidstr,@nowstr, @count)
					    select @count
				    end
			    else
				    -- 实际表中找到参照
				    begin
					    set @current=CONVERT(int,SUBSTRING(@tempno,@startindex+1,100))
					    set @current+=@count
					    insert into {tableName}(tablename, colname,statictext,machineidstr,nowstr, currentno) values(@tablename, @colname,@statictext,@machineidstr,@nowstr, @current)
					    select @current
				    end    
		        end
			    else
				    -- 缓存表里有值
				    begin
				    -- 判断时间戳是否匹配
				    if @currentnowstr=@nowstr
					    begin
						    -- 时间戳匹配
						    set @current += @count
						    update {tableName} set currentno = @current where tablename = @tablename and colname = @colname and statictext=@statictext
						    select @current					
					    end
				    else
					    begin
						    -- 时间戳不匹配,从新开始
						    set @current=@count
						    update {tableName} set currentno = @current,nowstr=@nowstr where tablename = @tablename and colname = @colname and statictext=@statictext
						    select @current	
					    end				
			    end
			    exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
			    commit;
			    return;
		end try
		begin catch
			exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
			select @err_message=ERROR_MESSAGE(),@err_serverity=ERROR_SEVERITY(),@err_state=ERROR_STATE();
			RAISERROR (@err_message, @err_serverity,@err_state );
            rollback;
			INSERT INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
				values('{procName}',@lockstr,ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(), ERROR_LINE() ,ERROR_MESSAGE(),GetDate());			
			return;
		end catch
	end
else
	begin
		-- 未获取到锁
		rollback;
		INSERT INTO {errTableName}(err_proc,lock_str,err_msg,err_time) 
				values('{procName}',@lockstr,'未获取到锁,申请锁返回:'+Convert(varchar,@result),GetDate());
		select @err_message='20秒内未获取到锁:'+@lockstr,@err_serverity=10,@err_state=1;
		RAISERROR (@err_message, 16,1 );
		return;
	end");
            #endregion
        }
        return true;
    }

    public async Task<long> NewIdAsync(DBAccess db, string tableName, string colName, CancellationToken cancellationToken = default)
    {
        await EnsureInitGenerator(db);
        var tableObjName = db.ParseQuotedName(tableName);
        var colObjName = db.ParseQuotedName(colName);
        var id = await db.SelectScalarAsync<long>($"exec {DBCacheGeneratorIdProcedureName} '{db.ProtectStringToSeg(tableObjName.Name)}','{db.ProtectStringToSeg(colObjName.Name)}',1", cancellationToken);
        return id;
    }

    public async Task<long[]> NewIdsAsync(DBAccess db, string tableName, string colName, int count, CancellationToken cancellationToken = default)
    {
        if (count < 1) throw new Exception("批量生成的数量最小为1!");
        await EnsureInitGenerator(db);
        var tableObjName = db.ParseQuotedName(tableName);
        var colObjName = db.ParseQuotedName(colName);
        var id = await db.SelectScalarAsync<long>($"exec {DBCacheGeneratorIdProcedureName} '{db.ProtectStringToSeg(tableObjName.Name)}','{db.ProtectStringToSeg(colObjName.Name)}',{count}");
        var res = new long[count];
        for (long i = id - count + 1, index = 0; i <= id; i++, index++) res[index] = i;
        return res;
    }

    /// <summary>
    /// 解析格式准备流水号生成
    /// </summary>
    /// <param name="format"></param>
    /// <param name="now"></param>
    /// <param name="machineIdString"></param>
    /// <returns></returns>
    private (string likestr, DateTime snoNow, int startindex, string statictext, string machineIdString) PrePareForSNO(SerialFormat format, DateTime now, string machineIdString)
    {
        if (machineIdString.IsNullOrWhiteSpace()) machineIdString = DotNetCommon.Machine.MachineIdString;
        var (likestr, snoNow, startindex) = SerialFormat.Parse(format, now, machineIdString);
        var statictext = "";
        var chunks = format.Chunks;
        for (int i = 0, len = chunks.Count; i < len; i++)
        {
            var _chunk = chunks[i];
            if (_chunk.Type == SerialFormatChunkType.StaticText)
            {
                statictext += _chunk.FormatString;
            }
            else if (_chunk.Type == SerialFormatChunkType.MachineText)
            {
                statictext += machineIdString.PadLeft(4, '0');
            }
        }
        return (likestr, snoNow, startindex, statictext, machineIdString);
    }

    public async Task<string> NewSNOAsync(DBAccess db, string tableName, string colName, SerialFormat serialFormat, CancellationToken cancellationToken = default)
    {
        SerialFormat.ValidFormat(serialFormat);
        await EnsureInitGenerator(db);
        var tableObjName = db.ParseQuotedName(tableName);
        var colObjName = db.ParseQuotedName(colName);
        var now = DateTime.Now;
        var (likestr, snoNow, startindex, statictext, machineIdString) = PrePareForSNO(serialFormat, now, DotNetCommon.Machine.MachineIdString);
        var sql = $@" 
exec {DBCacheGeneratorSNOProcedureName} 
	@tablename='{db.ProtectStringToSeg(tableObjName.Name)}',
	@colname='{db.ProtectStringToSeg(colObjName.Name)}',
	@statictext='{statictext}',
	@machineidstr={machineIdString},
	@nowstr='{snoNow.ToString(serialFormat.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.DateText).FormatString)}', 
	@likestr='{likestr}',
	@startindex={startindex},
	@count=1";
        long no = await db.SelectScalarAsync<long>(sql, cancellationToken);

        var chunk = serialFormat.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
        var sno = likestr.Substring(0, startindex);
        var s = no.ToString();
        if (s.Length > chunk.Length)
        {
            sno += s;
        }
        else
        {
            sno += s.PadLeft(chunk.Length, '0');
        }
        return sno;
    }

    public async Task<string[]> NewSNOsAsync(DBAccess db, string tableName, string colName, SerialFormat serialFormat, int count, CancellationToken cancellationToken = default)
    {
        if (count < 1) throw new Exception("批量生成的数量最小为1!");
        SerialFormat.ValidFormat(serialFormat);
        await EnsureInitGenerator(db);
        var tableObjName = db.ParseQuotedName(tableName);
        var colObjName = db.ParseQuotedName(colName);
        var now = DateTime.Now;
        var (likestr, snoNow, startindex, statictext, machineIdString) = PrePareForSNO(serialFormat, now, DotNetCommon.Machine.MachineIdString);
        var sql = $@" 
exec {DBCacheGeneratorSNOProcedureName} 
	@tablename='{db.ProtectStringToSeg(tableObjName.Name)}',
	@colname='{db.ProtectStringToSeg(colObjName.Name)}',
	@statictext='{statictext}',
	@machineidstr={machineIdString},
	@nowstr='{snoNow.ToString(serialFormat.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.DateText).FormatString)}', 
	@likestr='{likestr}',
	@startindex={startindex},
	@count={count}";

        long no = long.Parse(await db.SelectScalarAsync<string>(sql, cancellationToken));
        var chunk = serialFormat.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
        var res = new string[count];
        for (long i = no - count + 1, index = 0; i <= no; i++, index++)
        {
            var sno = likestr.Substring(0, startindex);
            var s = i.ToString();
            if (s.Length > chunk.Length)
            {
                sno += s;
            }
            else
            {
                sno += s.PadLeft(chunk.Length, '0');
            }
            res[index] = sno;
        }
        return res;
    }

    public void ClearCache(DBAccess db)
    {
        db.ExecuteSql(db.Manage.DropTableIfExistSql(DBCacheGeneratorIdTableName));
        db.ExecuteSql(db.Manage.DropTableIfExistSql(DBCacheGeneratorSNOTableName));
        db.ExecuteSql(db.Manage.DropTableIfExistSql(DBCacheGeneratorLogTableName));
        db.ExecuteSql(db.Manage.DropProcedureIfExistSql(DBCacheGeneratorIdProcedureName));
        db.ExecuteSql(db.Manage.DropProcedureIfExistSql(DBCacheGeneratorSNOProcedureName));
        _initGenerators.TryRemove(db.DBConn, out _);
    }
}
